package javacommon.excelUtils;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.BeansException;
import org.springframework.core.io.support.PropertiesLoaderUtils;

import com.lims.landInfo.action.ExcelAction;
import com.lims.landInfo.model.Land;
@SuppressWarnings({"unused","unchecked"})
public class XmlReader {
	private static Logger logger = Logger.getLogger(XmlReader.class);
	/*properties*/
	private static Properties PRO;
	private XmlReader(){}
	static {
		try {
			/**properties 初始化**/
			//PRO.load(XmlReader.class.getClassLoader().getResourceAsStream("excelToBean.properties"));
			PRO = PropertiesLoaderUtils.loadAllProperties("javacommon/excelUtils/config/excelToBean.properties");
		} catch (IOException e) {
			System.out.println("----配置文件加载错误-------------------------------------------");
			e.printStackTrace();
			logger.error(e);
		}
	}
	
	/*取配置文件excelToBean.properties的值**/
	public static Object getVale(String key) {
		return PRO.getProperty(key);
	}
	
	/***
	 * 从配置文件取出信息--》组装Map
	 * map{"classFullName"："com....",sheetIndex:"1","startRow":"1"
	 * 		beanMap:map{"col1":"attr1","col2":"attr2".....}
	 * }
	 * @param propertBeanName  javabean名称，与配置文件excelToBean.properties中的内容对应
	 * @return
	 */
	public static Map<String, Object> xmlToBeanMap(String propertBeanName) {
		SAXReader reader = new SAXReader();
		String path = PRO.getProperty(propertBeanName);
		Map<String, Object> rootMap = new HashMap<String, Object>();
		try {
			Document doc = reader.read(XmlReader.class.getClassLoader().getResourceAsStream(path));
			Element root = doc.getRootElement();
			Element el = root.element("class");
			String classFullName = el.attribute("name").getText();
			rootMap.put("classFullName", classFullName);
			String sheetIndex = el.attribute("sheetIndex").getText();
			rootMap.put("sheetIndex", sheetIndex);
			String startRow = el.attribute("startRow").getText();
			rootMap.put("startRow", startRow);
			Map<String, String> beanMap = new HashMap<String, String>();
			Element child = null;
			for(Iterator<?> iter = el.elementIterator(); iter.hasNext();){
				child = (Element) iter.next();
				beanMap.put(child.attribute("col").getText(), child.attribute("name").getText());
			}
			rootMap.put("beanMap", beanMap);
		} catch (DocumentException e) {
			logger.error(e);
			e.printStackTrace();
		}
		return rootMap;
	}
	
	/***
	 * 构建为BeanList
	 * @param inputStream
	 * @param propertBeanName
	 * @return
	 */
	public static List ExcelToList(InputStream inputStream,String propertBeanName) {
		Map map = xmlToBeanMap(propertBeanName); //取出已组装的map
		List beanList = new ArrayList();
		try {
			POIFSFileSystem fs = new POIFSFileSystem(inputStream);
			HSSFWorkbook wb = new HSSFWorkbook(fs);
			String sheetIndex = (String) map.get("sheetIndex");  //取出配置文件中设置的sheetIndex页
			if(sheetIndex == null || "".equals(sheetIndex.trim())) {
				return null;
			}
			HSSFSheet sheet = wb.getSheetAt(Integer.valueOf(sheetIndex.trim()).intValue()-1);
			int lastRow = sheet.getLastRowNum();
			HSSFRow row = null;
			HSSFCell cell = null;
			int statrRow = map.get("startRow") == null ? 0 : (Integer.valueOf(((String)map.get("startRow")).trim()).intValue()-1);
			Map<String,Object> beanMap = (Map<String, Object>) map.get("beanMap"); //取出 beanMap
			Set<String> set = beanMap.keySet();
			String key = null;
			Object val = null;
			//bean全类名
			String classFullName = (String) map.get("classFullName");
			Class clazz = Class.forName(classFullName);
			Method[] methods = clazz.getMethods();
			for(int i=statrRow; i<=lastRow; i++) {
				row = sheet.getRow(i);
				Object obj = clazz.newInstance();
				for(Iterator<String> iter = set.iterator();iter.hasNext();) {
					key = iter.next();//取出配置文件中对应列值
					val = beanMap.get(key);//取出配置文件中对应bean属性名
					for (int j = 0; j < methods.length; j++) {
						Method method = methods[j];
						String methodName = method.getName();
						if(methodName.startsWith("set") && methodName.equals("set"+upperCaseFirst((String)val))) {
							Class[] classes = method.getParameterTypes();
							String parmTypeName = classes[0].getName();
							String cellValue = getCellFormatValue(row.getCell(Integer.valueOf(key.trim().trim()).intValue()-1)); //取得cell值
							//根据不同类型设置值
							if(parmTypeName.equals("java.lang.String")) {
								method.invoke(obj, cellValue);
							} else if(parmTypeName.equals("java.lang.Double")) {
								method.invoke(obj,"".equals(cellValue.trim()) ? null : Double.valueOf(cellValue));
							} else if(parmTypeName.equals("java.lang.Integer")) {
								method.invoke(obj,"".equals(cellValue.trim()) ? null : Integer.valueOf(Double.valueOf(cellValue).intValue()));
							} else if(parmTypeName.equals("java.util.Date")) {
								Date date = null;
								if(!"".equals(cellValue.trim())) {
									SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
									date = sdf.parse(cellValue.trim());
								} 
								method.invoke(obj,date);
							} else if(parmTypeName.equals("java.lang.Float")) {
								method.invoke(obj,"".equals(cellValue.trim()) ? null : Float.valueOf(cellValue));
							}
						}
					}
				}
				beanList.add(obj);
			}
		} catch (FileNotFoundException e) {
			logger.error(e);
			e.printStackTrace();
		} catch (IOException e) {
			logger.error(e);
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			logger.error(e);
			e.printStackTrace();
		} catch (InstantiationException e) {
			logger.error(e);
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			logger.error(e);
			e.printStackTrace();
		} catch (BeansException e) {
			logger.error(e);
			e.printStackTrace();
		} catch (NumberFormatException e) {
			logger.error(e);
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			logger.error(e);
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			logger.error(e);
			e.printStackTrace();
		} catch (ParseException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			if(inputStream != null ) {
				try {
					inputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}  
		return beanList;
	}
	
	public static void main(String[] args) {
		InputStream in = XmlReader.class.getClassLoader().getResourceAsStream("国有（集体）企业土地基本情况表.xls");
		List list = ExcelToList(in,"Land");
		int countRows = 0;
		for (Iterator iterator = list.iterator(); iterator.hasNext();) {
			Object obj = iterator.next();
			System.out.println(((Land)obj).getLandno());
			countRows ++;
		}
		System.out.println();
		System.out.println("共计行数："+countRows);
	}
	
	/**
	 * 获取单元格数据内容为字符串类型的数据
	 * @param cell Excel单元格
	 * @return String 单元格数据内容
	 */
	@SuppressWarnings("deprecation")
	private static String getStringCellValue(HSSFCell cell) {
		String strCell = "";
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_STRING:
			strCell = cell.getStringCellValue();
			break;
		case HSSFCell.CELL_TYPE_NUMERIC:
			strCell = String.valueOf(cell.getNumericCellValue());
			break;
		case HSSFCell.CELL_TYPE_BOOLEAN:
			strCell = String.valueOf(cell.getBooleanCellValue());
			break;
		case HSSFCell.CELL_TYPE_BLANK:
			strCell = "";
			break;
		default:
			strCell = "";
			break;
		}
		if (strCell.equals("") || strCell == null) {
			return "";
		}
		if (cell == null) {
			return "";
		}
		return strCell;
	}


	/**
	*根据HSSFCell类型设置数据 
	*@param cell Excel单元格    
	* @return String 单元格数据内容    
	*/  
	private static String getCellFormatValue(HSSFCell cell) {
		String cellvalue = "";
		if (cell != null) {
			// 判断当前Cell的Type
			switch (cell.getCellType()) {
			// 如果当前Cell的Type为NUMERIC
			case HSSFCell.CELL_TYPE_NUMERIC:
				cellvalue = String.valueOf(cell.getNumericCellValue());
				break;
			case HSSFCell.CELL_TYPE_FORMULA: {
				// 判断当前的cell是否为Date
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					// 如果是Date类型则，转化为Data格式
					// 方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
					// cellvalue = cell.getDateCellValue().toLocaleString();
					// 方法2：这样子的data格式是不带带时分秒的：2011-10-12
					Date date = cell.getDateCellValue();
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					cellvalue = sdf.format(date);
				} 
				break;
			} case HSSFCell.CELL_TYPE_STRING: // 如果当前Cell的Type为STRIN
				// 取得当前的Cell字符串
				cellvalue = cell.getRichStringCellValue().getString();
				break;
			default:// 默认的Cell值
				cellvalue = cell.getRichStringCellValue().getString();
				break;
			}
		} else {
			cellvalue = "";
		}
		return cellvalue;
	}
	
	/***
	 * 首字母大写
	 * @param str
	 * @return
	 */
	private static String upperCaseFirst(String str) {
		str =str.replaceFirst(str.substring(0, 1),str.substring(0, 1).toUpperCase()) ;
		return str;
	}
	
}
